package com.jfinal.kit;


import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.POIDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.Map.Entry;


public class PoiKit {

    private static final int HEADER_ROW = 1;
    private static final int MAX_ROWS = 65536;

    private String sheetName = "new sheet";
    private int cellWidth = 8000;
    private int headerRow;
    private String[] headers = new String[]{};
    private String[] columns;
    private List<?> data;

    public PoiKit(List<?> data) {
        this.data = data;
    }

    public static PoiKit with(List<?> data) {
        return new PoiKit(data);
    }

    public HSSFWorkbook export() {
        assert headers != null;
        assert columns != null;
        assert cellWidth >= 0;
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row = null;
        HSSFCell cell = null;
        if (headers.length > 0) {
            row = sheet.createRow(0);
            if (headerRow <= 0) {
                headerRow = HEADER_ROW;
            }
            headerRow = Math.min(headerRow, MAX_ROWS);
            for (int h = 0, lenH = headers.length; h < lenH; h++) {
                @SuppressWarnings("deprecation")
                Region region = new Region(0, (short) h, (short) headerRow - 1, (short) h);// 合并从第rowFrom行columnFrom列
                sheet.addMergedRegion(region);// 到rowTo行columnTo的区域
                // 得到所有区域
                sheet.getNumMergedRegions();
                if (cellWidth > 0) {
                    sheet.setColumnWidth(h, cellWidth);
                }
                cell = row.createCell(h);
                cell.setCellValue(headers[h]);
            }
        }
        if (data.size() == 0) {
            return wb;
        }
        for (int i = 0, len = data.size(); i < len; i++) {
            row = sheet.createRow(i + headerRow);
            Object obj = data.get(i);
            if (obj == null) {
                continue;
            }
            if (obj instanceof Map) {
                processAsMap(columns, row, obj);
            } else if (obj instanceof Model) {
                processAsModel(columns, row, obj);
            } else if (obj instanceof Record) {
                processAsRecord(columns, row, obj);
            }
        }
        return wb;
    }

    @SuppressWarnings("unchecked")
    private static void processAsMap(String[] columns, HSSFRow row, Object obj) {
        HSSFCell cell;
        Map<String, Object> map = (Map<String, Object>) obj;
        if (columns.length == 0) {// 未设置显示列，默认全部
            Set<String> keys = map.keySet();
            int columnIndex = 0;
            for (String key : keys) {
                cell = row.createCell(columnIndex);
                cell.setCellValue(map.get(key) + "");
                columnIndex++;
            }
        } else {
            for (int j = 0, lenJ = columns.length; j < lenJ; j++) {
                cell = row.createCell(j);
                cell.setCellValue(map.get(columns[j]) + "");
            }
        }
    }

    private static void processAsModel(String[] columns, HSSFRow row, Object obj) {
        HSSFCell cell;
        Model<?> model = (Model<?>) obj;
        Set<Entry<String, Object>> entries = model._getAttrsEntrySet();
        if (columns.length == 0) {// 未设置显示列，默认全部
            int columnIndex = 0;
            for (Entry<String, Object> entry : entries) {
                cell = row.createCell(columnIndex);
                cell.setCellValue(entry.getValue() + "");
                columnIndex++;
            }
        } else {
            for (int j = 0, lenJ = columns.length; j < lenJ; j++) {
                cell = row.createCell(j);
                cell.setCellValue(model.get(columns[j]) + "");
            }
        }
    }

    private static void processAsRecord(String[] columns, HSSFRow row, Object obj) {
        HSSFCell cell;
        Record record = (Record) obj;
        Map<String, Object> map = record.getColumns();
        if (columns.length == 0) {// 未设置显示列，默认全部
            record.getColumns();
            Set<String> keys = map.keySet();
            int columnIndex = 0;
            for (String key : keys) {
                cell = row.createCell(columnIndex);
                cell.setCellValue(record.get(key) + "");
                columnIndex++;
            }
        } else {
            for (int j = 0, lenJ = columns.length; j < lenJ; j++) {
                cell = row.createCell(j);
                cell.setCellValue(map.get(columns[j]) + "");
            }
        }
    }

    public static List<Record> getExcelRecord(String filePath, String columArray[], String dataColumnArray[]) throws IOException, InvalidFormatException {
        File file = new File(filePath);
        return getExcelRecord(file, columArray, dataColumnArray);
    }

    public static List<Record> getExcelRecord(File file, String columArray[], String dataColumnArray[]) throws IOException, InvalidFormatException {
        InputStream ips = new FileInputStream(file);
        POIDocument wb = null;
        Workbook workbook = WorkbookFactory.create(ips);
        Sheet sheet = workbook.getSheetAt(0);

        //以下两数组一一对应
//        String columArray[] = {"日期", "应用", "系统平台", "渠道", "子渠道", "安装数", "注册数", "注册率", "次留数", "次留率"};
//        String dbColumArary[] = {"dataTime", "appName", "platform", "affiliateId", "subAffiliateId", "conversion", "registration",
//                "registrationRate", "retentionDay1", "retentionDay1Rate"};
        Map<Integer, String> dbColumnMap = new HashMap<>(); // Maps.newHashMap();

        int rows = sheet.getLastRowNum();
        if (rows < 2) {
            //报错
            return new ArrayList<>();
        }

        //处理表头
        List<String> columnNameList = new ArrayList<>(); // Lists.newArrayList(columArray);
        for (String col: columArray) {
            columnNameList.add(col);
        }
        Row nameRow = sheet.getRow(0);
        if (nameRow == null) {
            return new ArrayList<>();
        }
        for (Iterator itet = nameRow.cellIterator(); itet.hasNext(); ) {
            Cell cell = (Cell) itet.next();
            Integer index = cell.getColumnIndex();
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String columnName = cell.getRichStringCellValue().toString();
                columnName = columnName == null ? "" : columnName.trim().toUpperCase();

                Integer indexOfColumn = columnNameList.indexOf(columnName);
                if (indexOfColumn == null || indexOfColumn < 0 || indexOfColumn > dataColumnArray.length) {
                    //报错
                    System.out.println("parse excel file error:" + file.getName());
                    continue;
                }
                dbColumnMap.put(index, dataColumnArray[indexOfColumn]);
            }
            //报错
        }
        List<Record> records = new ArrayList<>();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                break;
            }
            Record record = new Record();
            Iterator<Cell> iterator = row.cellIterator();
            while (iterator.hasNext()) {
                Cell cell = iterator.next();
                Integer columnIndex = cell.getColumnIndex();
                String dbColumn = dbColumnMap.get(columnIndex);
                if (!StrKit.isBlank(dbColumn)) {
                    Object value = getCellValue(cell);
                    record.set(dbColumn, value);
                }
            }
            records.add(record);
        }
        return records;
    }

    private static Object getCellValue(Cell cell) {
        Object object = null;
        switch(cell.getCellType()){
            case Cell.CELL_TYPE_BOOLEAN:
                //得到Boolean对象的方法
                object = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //先看是否是日期格式
                if(DateUtil.isCellDateFormatted(cell)){
                    //读取日期格式
                    object = cell.getDateCellValue();
                }else{
                    //读取数字
                    object = cell.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                //读取公式
                try {
                    object = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    object = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                object = cell.getRichStringCellValue().toString();
                break;
        }
        return object;
    }

    public PoiKit sheetName(String sheetName) {
        this.sheetName = sheetName;
        return this;
    }

    public PoiKit cellWidth(int cellWidth) {
        this.cellWidth = cellWidth;
        return this;
    }

    public PoiKit headerRow(int headerRow) {
        this.headerRow = headerRow;
        return this;
    }

    public PoiKit headers(String[] headers) {
        this.headers = headers;
        return this;
    }

    public PoiKit columns(String[] columns) {
        this.columns = columns;
        return this;
    }

}
